"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 功能测试 -- gsql执行统计时间测试
Case Name   : 创建列存表，执行DDL和DML,查看db_tim和子时间的误差在10%以内
Create At   : 2023-12-11
Owner       : @kyeleze
Description :
    1. 创建列存表，执行DDL和DML
    2. 执行ddl sql
    3. 执行dml sql
    4. 查看每个sql的db_tim和子时间的误差
Expect      :
    1. 执行成功
    2. 成功
    3. 成功
    4. 误差在10%以内
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class DBTimeCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name = 't_dbtime_test_015'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        step = '----清理统计信息----'
        self.log.info(step)
        clean_sql = "select * from reset_unique_sql('global', 'all', 0);"
        result = self.primary_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('t', result.strip().splitlines()[2], '执行失败' + step)

    def test_sql_dbtime(self):
        step = '----step1:创建列存表，执行DDL和DML expect:执行成功----'
        self.log.info(step)
        create_table = f"drop table if exists {self.table_name};" \
                       f"create table {self.table_name} (col_sint smallint," \
                       f"col_int integer, col_bint bigint, col_dec decimal," \
                       f"col_num numeric, col_rel real, " \
                       f"col_dou double precision, col_sser smallserial," \
                       f"col_ser serial, col_bser bigserial, col_mon money," \
                       f"col_var varchar(200), col_char char(320), " \
                       f"col_char2 char, col_text text, col_nvchar nvarchar2," \
                       f"col_time1 timestamp with time zone," \
                       f"col_time2 timestamp without time zone," \
                       f"col_date date, col_time3 time without time zone," \
                       f"col_time4 time with time zone, " \
                       f"col_interval interval, col_clob clob," \
                       f"col_reltime reltime, col_cidr cidr, col_inet inet," \
                       f"col_bit bit(10), col_bitvar bit varying)" \
                       f"with (orientation=column, compression=no);"
        result = self.primary_sh.execut_db_sql(create_table,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result,
                      '执行失败' + step)

        step = '----step2:执行ddl sql expect:成功----'
        self.log.info(step)
        ddl_sql = f"create index {self.table_name}_idx1 on " \
                  f"{self.table_name} (col_bint);" \
                  f"alter table {self.table_name} add constraint " \
                  f"{self.table_name}_pkey primary key (col_int);" \
                  f"drop index {self.table_name}_idx1;"
        result = self.primary_sh.execut_db_sql(ddl_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      '执行失败' + step)
        self.assertIn(self.constant.DROP_INDEX_SUCCESS_MSG, result,
                      '执行失败' + step)

        step = '----step3:执行dml sql expect:成功----'
        dml_sql = f"insert into {self.table_name} values (1,2,3,1,1,1,1,1," \
                  f"1,1,1,'1','1','1','1','1',date'2020-08-05', " \
                  f"date'2020-08-05',date'2020-08-05',date'2020-08-05'," \
                  f"'21:21:21 pst',interval '3 hours','1', '90', " \
                  f"'192.168.100.128/25', '192.168.31.32/24', " \
                  f"b'1001110101', b'10110010101110');" \
                  f"select count(*) from {self.table_name};" \
                  f"update {self.table_name} set " \
                  f"col_char='abcdefghijklmnopqrstuvwxyz1234567890' " \
                  f"where col_sint=1;" \
                  f"delete from {self.table_name} where col_int=1;" \
                  f"drop table {self.table_name};"
        result = self.primary_sh.execut_db_sql(dml_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertIn('1 row', result, '执行失败' + step)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, result,
                      '执行失败' + step)

        step = '--step2:查看每个sql的db_tim和子时间的误差 expect:误差在10%以内--'
        self.log.info(step)
        sql_list = [f'create index {self.table_name}_idx1',
                    f'alter table {self.table_name}',
                    f'drop index {self.table_name}_idx1',
                    f'create table {self.table_name}',
                    f'insert into {self.table_name}',
                    f'select * from {self.table_name}',
                    f'update {self.table_name}',
                    f'drop table {self.table_name}']
        for sql in sql_list:
            check_sql = f"select (select round((db_time-total_time)/db_time," \
                        f"2) from (select db_time, sum(execution_time + " \
                        f"parse_time + plan_time + rewrite_time + " \
                        f"pl_execution_time + pl_compilation_time + " \
                        f"data_io_time + net_send_time + srt1_q + " \
                        f"srt2_simple_query + srt3_analyze_rewrite + " \
                        f"srt4_plan_query + srt5_light_query + srt6_p + " \
                        f"srt7_b + srt8_e + srt9_d + srt10_s + srt11_c + " \
                        f"srt12_u + srt13_before_query + srt14_after_query) " \
                        f"as total_time from get_instr_unique_sql() where " \
                        f"query like '{sql}%' group by db_time)) < 0.1 as res;"
            result = self.primary_sh.execut_db_sql(check_sql,
                                                   sql_type=self.user_param)
            self.log.info(result)
            self.assertIn('', result.strip().splitlines()[2],
                          '执行失败' + step)

    def tearDown(self):
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
